package com.wencst.generateJPA.database.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.Properties;

import com.wencst.generateJPA.bean.ColumnBean;
import com.wencst.generateJPA.bean.TableBean;
import com.wencst.generateJPA.config.CoreContext;
import com.wencst.generateJPA.database.IDatabase;
import com.wencst.generateJPA.utils.StringUtils;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DefaultDB implements IDatabase{

    private Connection con;
    //获取数据库的元数据
  	private DatabaseMetaData db;
  	private List<String> tableNames;
    public DefaultDB(CoreContext coreContext) {
    	try {
    		Properties props =new Properties();
    		props.setProperty("user", coreContext.getUsername());
    		props.setProperty("password", coreContext.getPassword());
    		props.setProperty("remarks", "true"); //设置可以获取remarks信息 
    		props.setProperty("useInformationSchema", "true");//设置可以获取tables remarks信息
    		Class.forName(coreContext.getDriver());
			con = DriverManager.getConnection(
					coreContext.getURL(),
					props);
			db = con.getMetaData();
			tableNames = coreContext.lisAllTableNames();
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage(),e);
		}
    }
	@Override
	public List<TableBean> getAllTables() {
		if (tableNames == null || tableNames.size() <= 0) {
			tableNames = getAllTableNames();
		}
		List<TableBean> allTableBean = new ArrayList<>();
		for (String tableName : tableNames) {
			TableBean tb = getTableDetail(tableName);
			allTableBean.add(tb);
		}
		return allTableBean;
	}

	@Override
	public List<String> getAllTableNames() {
		List<String> tableNames = new ArrayList<String>();
		ResultSet rs = null;
        try {
			//是否有指定生成表，有指定则直接用指定表，没有则全表生成
			rs = db.getTables(null, null, null, new String[] { "TABLE" });
            while (rs.next()) {
            	tableNames.add(rs.getString("TABLE_NAME"));
            }
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		return tableNames;
	}

	@Override
	public List<ColumnBean> getAllColumn(String tableName) {
		List<ColumnBean> allColumns = new ArrayList<>();
        try (ResultSet rs = db.getColumns(null, null, tableName, "%")) {
            while (rs.next()) {
                ColumnBean column = new ColumnBean();
                column.setColumnName(rs.getString("COLUMN_NAME"));
                column.setTypeCode(rs.getInt("DATA_TYPE"));
                column.setColumnType(rs.getString("TYPE_NAME"));
                column.setJavaType(StringUtils.sqlType2JavaType(column.getColumnType()));
                column.setColumnJavaName(StringUtils.trans2Hump(column.getColumnName(),false));
                column.setLength(rs.getInt("COLUMN_SIZE"));
                column.setDecimal(rs.getInt("DECIMAL_DIGITS"));
                // Oracle throws java.sql.SQLException: Invalid column name
                boolean autoIncrement = false;
                try {
                    String autoIncrementMetadata = rs.getString("IS_AUTOINCREMENT");
                    autoIncrement = autoIncrementMetadata != null && autoIncrementMetadata.equals("YES");
                } catch (Exception e) {
                	log.error(e.getMessage(),e);
                }
                column.setAutoIncrement(autoIncrement);

                try {
                    column.setDescription(Optional.ofNullable(rs.getString("REMARKS")));
                    column.setComment(column.getDescription().get());
                } catch (Exception e) {
                	log.error(e.getMessage(),e);
                }

                boolean nullable = true;
                try {
                    String isNullableMetadata = rs.getString("IS_NULLABLE");
                    nullable = isNullableMetadata == null || isNullableMetadata.equals("YES");
                } catch (Exception e) {
                	log.error(e.getMessage(),e);
                }
                column.setNullable(nullable);
                List<ColumnBean> primaryKeyNames = getAllPrimaryKeys(tableName);
                boolean primaryKey = false;
                try {
                    primaryKey = primaryKeyNames.stream()
                    		.filter(pk -> pk.getColumnName().equals(getValue(rs,"COLUMN_NAME")))
                    		.count() > 0;
                } catch (Exception e) {
                	log.error(e.getMessage(),e);
                }
                column.setPrimaryKey(primaryKey);
                allColumns.add(column);
            }
        } catch (SQLException e1) {
        	log.error(e1.getMessage(),e1);
		}
        return allColumns;
	}


    private String getValue(ResultSet rs, String columnName) {
        try {
            return rs.getString(columnName);
        } catch (SQLException e) {
        	log.error(e.getMessage(),e);
            return null;
        }
    }
	
	@Override
	public List<ColumnBean> getAllPrimaryKeys(String tableName) {
		List<ColumnBean> primaryKey = new ArrayList<>();
		ResultSet pkRSet = null;
        try {
			pkRSet = db.getPrimaryKeys(null, null, tableName);
            while( pkRSet.next() ) {
            	String columnName = pkRSet.getString(4);
            	ColumnBean cb = new ColumnBean();
            	cb.setColumnName(columnName);
            	primaryKey.add(cb);
            }
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} finally {
			try {
				if (pkRSet != null)
					pkRSet.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		return primaryKey;
	}

	@Override
	public TableBean getTableDetail(String tableName) {
		TableBean tb = new TableBean();
		ResultSet rs = null;
        try {
			//是否有指定生成表，有指定则直接用指定表，没有则全表生成
			rs = db.getTables(null, null, tableName, new String[] { "TABLE" });
            if (rs.next()) {
            	tb.setTableName(rs.getString("TABLE_NAME"));
            	tb.setComment(rs.getString("REMARKS"));
            	tb.setColumns(getAllColumn(tableName));
            	tb.setJavaBeanName(
            			StringUtils.trans2Hump(
            					tb.getTableName(), true));
            }
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		return tb;
	}
}
